<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>数据库基本增删改查语句总结 | haijd</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  <meta name="keywords" content="SQL Server" />
  
  
  
  
  <meta name="description" content="数据库基本增删改查语句总结">
<meta name="keywords" content="SQL Server">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库基本增删改查语句总结">
<meta property="og:url" content="http://www.hais2.com/2015/03/25/Added-the-database-search-statement-summary/index.html">
<meta property="og:site_name" content="haijd">
<meta property="og:description" content="数据库基本增删改查语句总结">
<meta property="og:locale" content="default">
<meta property="og:updated_time" content="2017-08-02T15:22:33.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="数据库基本增删改查语句总结">
<meta name="twitter:description" content="数据库基本增删改查语句总结">
  
    <link rel="alternate" href="/atom.xml" title="haijd" type="application/atom+xml">
  
  <link rel="icon" href="/css/images/favicon.ico">
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link href="https://fonts.googleapis.com/css?family=Open+Sans|Montserrat:700" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Roboto:400,300,300italic,400italic" rel="stylesheet" type="text/css">
  <link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">
  <style type="text/css">
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/9749f0/00000000000000000001008f/27/l?subset_id=2&fvd=n5) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/90cf9f/000000000000000000010091/27/l?subset_id=2&fvd=n7) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/8a5494/000000000000000000013365/27/l?subset_id=2&fvd=n4) format("woff2");font-weight:lighter;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/d337d8/000000000000000000010095/27/l?subset_id=2&fvd=i4) format("woff2");font-weight:400;font-style:italic;}</style>
    
  <link rel="stylesheet" id="athemes-headings-fonts-css" href="//fonts.googleapis.com/css?family=Yanone+Kaffeesatz%3A200%2C300%2C400%2C700&amp;ver=4.6.1" type="text/css" media="all">
  <link rel="stylesheet" href="/css/style.css">

  <script src="/js/jquery-3.1.1.min.js"></script>

  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="/css/bootstrap.css" >
  <link rel="stylesheet" href="/css/hiero.css" >
  <link rel="stylesheet" href="/css/glyphs.css" >
  
    <link rel="stylesheet" href="/css/vdonate.css" >
  

</head>

<script>
var themeMenus = {};

  themeMenus["/"] = "Home"; 

  themeMenus["/archives"] = "Archives"; 

  themeMenus["/categories"] = "Categories"; 

  themeMenus["/tags"] = "Tags"; 

  themeMenus["/about"] = "About"; 

</script>


  <body data-spy="scroll" data-target="#toc" data-offset="50">


  <header id="allheader" class="site-header" role="banner">
  <div class="clearfix container">
      <div class="site-branding">

          <h1 class="site-title">
            
              <a href="/" title="haijd" rel="home"> haijd </a>
            
          </h1>

          
            <div class="site-description">Stay Hungry,Stay Foolish</div>
          
            
          <nav id="main-navigation" class="main-navigation" role="navigation">
            <a class="nav-open">Menu</a>
            <a class="nav-close">Close</a>
            <div class="clearfix sf-menu">

              <ul id="main-nav" class="nmenu sf-js-enabled">
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/">Home</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/archives">Archives</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/categories">Categories</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/tags">Tags</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/about">About</a> </li>
                    
              </ul>
            </div>
          </nav>


      </div>
  </div>
</header>




  <div id="container">
    <div id="wrap">
            
      <div id="content" class="outer">
        
          <section id="main" style="float:none;"><article id="post-Added-the-database-search-statement-summary" style="width: 66%; float:left;" class="article article-type-post" itemscope itemprop="blogPost" >
  <div id="articleInner" class="clearfix post-1016 post type-post status-publish format-standard has-post-thumbnail hentry category-template-2 category-uncategorized tag-codex tag-edge-case tag-featured-image tag-image tag-template">
    
    
      <header class="article-header">
        
  
    <h1 class="thumb" class="article-title" itemprop="name">
      数据库基本增删改查语句总结
    </h1>
  

      </header>
    
    <div class="article-meta">
      
	Posted on <a href="/2015/03/25/Added-the-database-search-statement-summary/" class="article-date">
	  <time datetime="2015-03-25T12:27:07.000Z" itemprop="datePublished">March 25, 2015</time>
	</a>

      
	<span id="busuanzi_container_page_pv">
	  本文总阅读量<span id="busuanzi_value_page_pv"></span>次
	</span>

    </div>
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="创建-create"><a href="#创建-create" class="headerlink" title="创建 create"></a>创建 create</h2><figure class="highlight routeros"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">    --创建数据库</span><br><span class="line">create database MyDatabase</span><br><span class="line">on primary</span><br><span class="line">(</span><br><span class="line">    <span class="attribute">name</span>=<span class="string">'MyDatabase_data'</span>,</span><br><span class="line">    <span class="attribute">filename</span>=<span class="string">'D:\working software\sql Sever\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDatabase0301_data.mdf'</span>,</span><br><span class="line">    <span class="attribute">size</span>=5MB,</span><br><span class="line">    <span class="attribute">filegorwth</span>=1MB</span><br><span class="line">)</span><br><span class="line">    --创建日志</span><br><span class="line">log on</span><br><span class="line">(</span><br><span class="line">    <span class="attribute">name</span>=<span class="string">'MyDatabase_log'</span>,</span><br><span class="line">    <span class="attribute">filename</span>=<span class="string">'D:\working software\sql Sever\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDatabase0301_data_log.ldf'</span>,</span><br><span class="line">    <span class="attribute">size</span>=1MB,</span><br><span class="line">    <span class="attribute">filegorwth</span>=10%</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<a id="more"></a>
<h2 id="使用某个数据库"><a href="#使用某个数据库" class="headerlink" title="使用某个数据库"></a>使用某个数据库</h2><figure class="highlight actionscript"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">use</span> MyDatabase</span><br></pre></td></tr></table></figure>
<h2 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h2><figure class="highlight lsl"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">carate table Student</span><br><span class="line">(</span><br><span class="line">    StuId int identity(<span class="number">1</span>,<span class="number">1</span>) primary <span class="type">key</span>, --主键，自增从<span class="number">1</span>开始每次增加<span class="number">1</span></span><br><span class="line">    StuName nvarchar(<span class="number">10</span>),</span><br><span class="line">    StuGender char(<span class="number">2</span>),</span><br><span class="line">    StuAge int,</span><br><span class="line">    StuChinese int,</span><br><span class="line">    StuMath int,</span><br><span class="line">    StuEnglish int</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<h2 id="增-三种方式-inster-into"><a href="#增-三种方式-inster-into" class="headerlink" title="增(三种方式) inster into"></a>增(三种方式) inster into</h2><figure class="highlight crystal"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">--第一种</span><br><span class="line">inster into Student(StuName.StuGender,StuAge) values(<span class="string">'Microsoft'</span>,<span class="string">'gay'</span>,<span class="number">30</span>,<span class="number">0</span>,<span class="number">0</span>,<span class="number">0</span>)</span><br><span class="line">--第二种</span><br><span class="line">inster into Student values(<span class="string">'Microsoft'</span>,<span class="string">'gay'</span>,<span class="number">30</span>,<span class="number">0</span>,<span class="number">0</span>,<span class="number">0</span>)</span><br><span class="line">--第三种</span><br><span class="line">inster into Student(StuName,StuGender,StuAge)</span><br><span class="line"><span class="keyword">select</span> <span class="string">'Microsoft'</span>,<span class="string">'gay'</span>,<span class="number">30</span>,<span class="number">0</span>,<span class="number">0</span>,<span class="number">0</span> <span class="class"><span class="keyword">union</span></span></span><br><span class="line"><span class="keyword">select</span> <span class="string">'Google'</span>,<span class="string">'gay'</span>,<span class="number">20</span>,<span class="number">10</span>,<span class="number">10</span>,<span class="number">10</span> <span class="class"><span class="keyword">union</span></span></span><br><span class="line"><span class="keyword">select</span> <span class="string">'FaceBook'</span>,<span class="string">'gay'</span>,<span class="number">11</span>,<span class="number">20</span>,<span class="number">20</span>,<span class="number">20</span> <span class="class"><span class="keyword">union</span></span></span><br><span class="line"><span class="keyword">select</span> <span class="string">'Twitter'</span>,<span class="string">'gay'</span>,<span class="number">13</span>,<span class="number">30</span>,<span class="number">30</span>,<span class="number">30</span></span><br></pre></td></tr></table></figure>
<h2 id="删-三种方式"><a href="#删-三种方式" class="headerlink" title="删(三种方式)"></a>删(三种方式)</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--第一种，数据表一起删除掉</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> Student</span><br><span class="line"><span class="comment">--第二种，只删除数据，主键不重置为0，可以加后缀(where子句)</span></span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> Student </span><br><span class="line"><span class="comment">--第三种，只删除数据，主键重置为0</span></span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">from</span> Student</span><br></pre></td></tr></table></figure>
<h2 id="改-update"><a href="#改-update" class="headerlink" title="改 update"></a>改 update</h2><figure class="highlight routeros"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update Student <span class="builtin-name">set</span> <span class="attribute">StuAge</span>=20 where <span class="attribute">StuId</span>=2</span><br></pre></td></tr></table></figure>
<h2 id="添加约束-alter"><a href="#添加约束-alter" class="headerlink" title="添加约束 alter"></a>添加约束 alter</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--主键约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> <span class="keyword">constraint</span> PK_Student_StuId primary <span class="keyword">key</span>(StuId)</span><br><span class="line"><span class="comment">--唯一约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> <span class="keyword">constraint</span> UQ_Student_StuName <span class="keyword">unique</span> (StuName)</span><br><span class="line"><span class="comment">--默认约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> <span class="keyword">constraint</span> DF_Student_StuGender <span class="keyword">default</span> <span class="string">'gay'</span> <span class="keyword">for</span> StuGender</span><br><span class="line"><span class="comment">--检查约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> <span class="keyword">constraint</span> CK_Student_StuAge <span class="keyword">check</span>(StuAge&gt;<span class="number">0</span> <span class="keyword">add</span> StuAge&lt;<span class="number">150</span>)</span><br><span class="line"><span class="comment">--外键约束(前外键，后主键)</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> <span class="keyword">constraint</span> FK_Student_StuClass foreign <span class="keyword">key</span> (StuClass) <span class="keyword">references</span> tClass(classId)</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--手动删除一列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">drop</span> <span class="keyword">column</span> StuGender</span><br><span class="line"><span class="comment">--手动增加一列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">add</span> StuIsGay <span class="keyword">nvarchar</span>(<span class="number">2</span>) <span class="keyword">not</span> <span class="literal">null</span></span><br><span class="line"><span class="comment">--手动修改一列数据类型</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> Student <span class="keyword">alter</span> <span class="keyword">column</span> StuGender <span class="keyword">nchar</span>(<span class="number">2</span>)</span><br></pre></td></tr></table></figure>
<h2 id="查-select"><a href="#查-select" class="headerlink" title="查 select"></a>查 select</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--查询列表中所有的数据</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student</span><br><span class="line"><span class="comment">--查询指定列</span></span><br><span class="line"><span class="keyword">select</span> StuName,StuGender,StuAge <span class="keyword">from</span> Student</span><br><span class="line"></span><br><span class="line"><span class="comment">--给查询出的结果以别名显示(四种方式)</span></span><br><span class="line"><span class="keyword">select</span> StuName <span class="keyword">as</span> <span class="string">'姓名'</span>,StuGender <span class="keyword">as</span> <span class="string">'性别'</span>,StuAge <span class="keyword">as</span> <span class="string">'年龄'</span> <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> StuName <span class="keyword">as</span> 姓名，StuGender <span class="keyword">as</span> 性别,StuAge <span class="keyword">as</span> 年龄 <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> StuName 姓名，StuGender 性别,StuAge 年龄 <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> 姓名=StuName,性别=StuGender,年龄=StuAge <span class="keyword">from</span> Student</span><br><span class="line"></span><br><span class="line"><span class="comment">--前N条数据 top order by</span></span><br><span class="line"><span class="keyword">select</span> top <span class="number">5</span> * <span class="keyword">from</span> Student <span class="keyword">order</span> <span class="keyword">by</span> StuAge (<span class="keyword">asc</span>)  <span class="comment">--从小到大</span></span><br><span class="line"><span class="keyword">select</span> top <span class="number">5</span> * <span class="keyword">from</span> Student <span class="keyword">order</span> <span class="keyword">by</span> StuAge (<span class="keyword">desc</span>)  <span class="comment">--从大到小</span></span><br><span class="line"><span class="keyword">select</span> top <span class="number">5</span> <span class="keyword">percent</span> * <span class="keyword">from</span> Student <span class="keyword">order</span> <span class="keyword">by</span> StuAge (<span class="keyword">desc</span>)  <span class="comment">--百分比</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--去除重复</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> StuName <span class="keyword">from</span> Student</span><br><span class="line"></span><br><span class="line"><span class="comment">--类型相同时连接</span></span><br><span class="line"><span class="keyword">select</span> StuName+StuAge+StuGender <span class="keyword">from</span> Student</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">--聚合函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sum</span>(StuChinese) <span class="keyword">as</span> 语文总分 <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">avg</span>(StuMath) <span class="keyword">as</span> 数学平均分 <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">max</span>(StuEnglish) <span class="keyword">as</span> 英语最高分 <span class="keyword">from</span> Student </span><br><span class="line"><span class="keyword">select</span> <span class="keyword">min</span>(StuEnglish) <span class="keyword">as</span> 英语最低分 <span class="keyword">from</span> Student</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> Student   <span class="comment">--查询表中总共多少数据</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--between and双闭区间</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuAge <span class="keyword">between</span> <span class="number">20</span> <span class="keyword">and</span> <span class="number">30</span>  <span class="comment">--20岁到30岁之间的数据</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuAge&gt;=<span class="number">20</span> <span class="keyword">and</span> StuAge&lt;=<span class="number">30</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--in 规定多个值</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">in</span>(<span class="string">'Microsoft'</span>,<span class="string">'Google'</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment">--模糊查询</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">like</span> <span class="string">'东%'</span> <span class="comment">--%号替代一个或多个字符</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">like</span> <span class="string">'东_'</span> <span class="comment">--_符号代替一个字符</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">like</span> <span class="string">'东[a-z]'</span> <span class="comment">--限制范围</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">like</span> <span class="string">'东[^a-z]'</span> <span class="comment">--取非，排除该范围</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuName <span class="keyword">like</span> <span class="string">'东[_%]'</span>  <span class="comment">--取消转义</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--分组 group by</span></span><br><span class="line"><span class="keyword">select</span> StuId <span class="keyword">as</span> 学生号,<span class="keyword">count</span>(*) <span class="keyword">as</span> 学生总数 <span class="keyword">from</span> Student <span class="keyword">group</span> <span class="keyword">by</span> StuId</span><br><span class="line"><span class="comment">--分组后筛选 having</span></span><br><span class="line"><span class="keyword">select</span> StuID <span class="keyword">as</span> 学生号,<span class="keyword">count</span>(*) <span class="keyword">as</span> 学生总数 <span class="keyword">from</span> Student <span class="keyword">group</span> <span class="keyword">by</span> StuId <span class="keyword">having</span>(<span class="keyword">count</span>(*)&gt;<span class="number">2</span>)  <span class="comment">--筛选人数大于2的数据</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--时间函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">GETDATE</span>()   <span class="comment">--获得当前时间</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">DATEADD</span>(dd,<span class="number">-90</span>,<span class="keyword">GETDATE</span>())  <span class="comment">--可以在源日期值上追加指定时间间隔的日期数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">DATEDIFF</span>(dd,<span class="string">'2015-03-25'</span>,<span class="keyword">GETDATE</span>()) <span class="keyword">as</span> 过了几天  <span class="comment">--计算两个日期之间的间隔</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">DATENAME</span>(dw,<span class="keyword">GETDATE</span>())   <span class="comment">--获取日期的指定格式的字符串表现形式</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--联合结果集</span></span><br><span class="line">    <span class="comment">--union去除重复</span></span><br><span class="line">    <span class="keyword">select</span> StuId,StuName <span class="keyword">from</span> Student</span><br><span class="line">    <span class="keyword">union</span></span><br><span class="line">    <span class="keyword">select</span> BanGanBuId.BanGanBuName <span class="keyword">from</span> BanGanBu</span><br><span class="line">    <span class="keyword">union</span></span><br><span class="line">    <span class="keyword">select</span> XueShengHuiId,XueShengHuiName <span class="keyword">from</span> XueShengHui</span><br><span class="line">    <span class="comment">--union all不去除重复</span></span><br><span class="line">    <span class="keyword">select</span> StuId,StuName <span class="keyword">from</span> Student</span><br><span class="line">    <span class="keyword">union</span> all</span><br><span class="line">    <span class="keyword">select</span> BanGanBuId.BanGanBuName <span class="keyword">from</span> BanGanBu</span><br><span class="line">    <span class="keyword">union</span> all</span><br><span class="line">    <span class="keyword">select</span> XueShengHuiId,XueShengHuiName <span class="keyword">from</span> XueShengHui</span><br><span class="line">    </span><br><span class="line"><span class="comment">--字符串函数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">LEN</span>(<span class="string">'每天都要吃饭'</span>)   <span class="comment">--获取字符串长度或字符个数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">DATALENGTH</span>(<span class="string">'今天天气真好'</span>)   <span class="comment">--获取字符串字节数</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">LOWER</span>(<span class="string">'MICROSOFT'</span>)   <span class="comment">--大写转小写</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">UPPER</span>(<span class="string">'microsoft'</span>)   <span class="comment">--小写转大写</span></span><br><span class="line"><span class="comment">--去掉空格</span></span><br><span class="line">    <span class="keyword">select</span> <span class="string">'我是'</span>+<span class="keyword">LTRIM</span>(<span class="string">'  大侠  '</span>)   <span class="comment">--去掉左边空格</span></span><br><span class="line">    <span class="keyword">select</span> <span class="string">'我是'</span>+<span class="keyword">RTRIM</span>(<span class="string">'  小菜  '</span>)   <span class="comment">--去掉右边的空格</span></span><br><span class="line">    <span class="keyword">select</span> <span class="string">'我是'</span>+<span class="keyword">LTRIM</span>(<span class="keyword">RTRIM</span>(<span class="string">'  我  '</span>))   <span class="comment">--去掉两边空格</span></span><br><span class="line"><span class="comment">--截取</span></span><br><span class="line">    <span class="keyword">select</span> <span class="keyword">LEFT</span>(<span class="string">'我是好人啊'</span>,<span class="number">1</span>)   <span class="comment">--从字符串左边开始截取1个</span></span><br><span class="line">    <span class="keyword">select</span> <span class="keyword">RIGHT</span>(<span class="string">'我是好人啊'</span>,<span class="number">1</span>)   <span class="comment">--从字符串右边开始截取1个</span></span><br><span class="line">    <span class="keyword">select</span> <span class="keyword">SUBSTRING</span>(<span class="string">'我是好人啊'</span>,<span class="number">1</span>,<span class="number">4</span>)  <span class="comment">--从第一个字符开始截取，截取4个</span></span><br></pre></td></tr></table></figure>
<h2 id="SQL语句的执行顺序"><a href="#SQL语句的执行顺序" class="headerlink" title="SQL语句的执行顺序"></a>SQL语句的执行顺序</h2><p>　　1.from 表<br>　　2.where 条件<br>　　3.group by 列<br>　　4.having 筛选条件<br>　　5.select<br>　　　　5.1 选择列<br>　　　　5.2 distinct<br>　　　　5.3 top(应用top选项最后计算)<br>　　6.order by 排序</p>
<h2 id="select语句处理顺序"><a href="#select语句处理顺序" class="headerlink" title="select语句处理顺序"></a>select语句处理顺序</h2><p>　　1.FROM<br>　　2.ON<br>　　3.JOIN<br>　　4.WHERE<br>　　5.GROUP BY<br>　　6.WITH CUBE 或 WITH ROLLUP<br>　　7.HAVING<br>　　8.SELECT<br>　　9.DISTINCT<br>　　10.RODER BY<br>　　11.TOP</p>
<h2 id="数据类型"><a href="#数据类型" class="headerlink" title="数据类型"></a>数据类型</h2><p>　　char：汉字1，字母2，多余空间会用空格补全<br>　　varchar：汉字1，字母2<br>　　nchar：(n表示Unicode)汉字1，字母1，多余空间会用空格补全<br>　　nvarchar：(n表示Unicode)汉字1，字母1</p>
<h2 id="null的特殊处理-null作为“不知道”处理"><a href="#null的特殊处理-null作为“不知道”处理" class="headerlink" title="null的特殊处理(null作为“不知道”处理)"></a>null的特殊处理(null作为“不知道”处理)</h2><figure class="highlight n1ql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuMath <span class="keyword">is</span> <span class="literal">null</span>  </span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> Student <span class="keyword">where</span> StuMath <span class="keyword">is</span> <span class="keyword">not</span> <span class="literal">null</span></span><br><span class="line"><span class="keyword">select</span> <span class="literal">null</span>+<span class="number">1</span>   --返回<span class="literal">null</span></span><br></pre></td></tr></table></figure>
      
    </div>
    <footer class="entry-meta entry-footer">
      
	<span class="ico-folder"></span>
    <a class="article-category-link" href="/categories/SQL/">SQL</a>

      
  <span class="ico-tags"></span>
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/SQL-Server/">SQL Server</a></li></ul>

      
        <div id="donation_div"></div>

<script src="/js/vdonate.js"></script>
<script>
var a = new Donate({
  title: '如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作!', // 可选参数，打赏标题
  btnText: 'Donate', // 可选参数，打赏按钮文字
  el: document.getElementById('donation_div'),
  wechatImage: '/css/images/wechatPay.png',
  alipayImage: '/css/images/alipay.jpg'
});
</script>
      

      
        
	<section id="comments" class="comment">
	  <div id="disqus_thread">
	  <noscript>Please enable JavaScript to view the <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
	  </div>
	</section>

	<script type="text/javascript">
	var disqus_shortname = 'haijdblog';
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/count.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	</script>


      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2015/03/26/Sql-Stored-procedure/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          存储过程
        
      </div>
    </a>
  
  
    <a href="/2015/02/11/ASP-NET-Url-Rewriting/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">ASP.NET中的Url重写</div>
    </a>
  
</nav>

  
</article>

<!-- Table of Contents -->

  <aside id="sidebar">
    <div id="toc" class="toc-article">
    <strong class="toc-title">Contents</strong>
    
      <ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#创建-create"><span class="nav-number">1.</span> <span class="nav-text">创建 create</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#使用某个数据库"><span class="nav-number">2.</span> <span class="nav-text">使用某个数据库</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#创建表"><span class="nav-number">3.</span> <span class="nav-text">创建表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#增-三种方式-inster-into"><span class="nav-number">4.</span> <span class="nav-text">增(三种方式) inster into</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#删-三种方式"><span class="nav-number">5.</span> <span class="nav-text">删(三种方式)</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#改-update"><span class="nav-number">6.</span> <span class="nav-text">改 update</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#添加约束-alter"><span class="nav-number">7.</span> <span class="nav-text">添加约束 alter</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#查-select"><span class="nav-number">8.</span> <span class="nav-text">查 select</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL语句的执行顺序"><span class="nav-number">9.</span> <span class="nav-text">SQL语句的执行顺序</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#select语句处理顺序"><span class="nav-number">10.</span> <span class="nav-text">select语句处理顺序</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数据类型"><span class="nav-number">11.</span> <span class="nav-text">数据类型</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#null的特殊处理-null作为“不知道”处理"><span class="nav-number">12.</span> <span class="nav-text">null的特殊处理(null作为“不知道”处理)</span></a></li></ol>
    
    </div>
  </aside>
</section>
        
      </div>
      <footer id="footer" class="site-footer">
  

  <div class="clearfix container">
      <div class="site-info">
	      &copy; 2018 haijd All Rights Reserved.
          
            <span id="busuanzi_container_site_uv">
              本站访客数<span id="busuanzi_value_site_uv"></span>人次  
              本站总访问量<span id="busuanzi_value_site_pv"></span>次
            </span>
          
      </div>
      <div class="site-credit">
        Theme by <a href="https://github.com/iTimeTraveler/hexo-theme-hiero" target="_blank">hiero</a>
      </div>
  </div>
</footer>


<!-- min height -->

<script>
    var contentdiv = document.getElementById("content");

    contentdiv.style.minHeight = document.body.offsetHeight - document.getElementById("allheader").offsetHeight - document.getElementById("footer").offsetHeight + "px";
</script>
    </div>
    <!-- <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
    <a href="/categories" class="mobile-nav-link">Categories</a>
  
    <a href="/tags" class="mobile-nav-link">Tags</a>
  
    <a href="/about" class="mobile-nav-link">About</a>
  
</nav> -->
    

<!-- mathjax config similar to math.stackexchange -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$','$'], ["\\(","\\)"] ],
      processEscapes: true
    }
  });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for(i=0; i < all.length; i += 1) {
            all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>

<script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/scripts.js"></script>
<script src="/js/bootstrap.js"></script>
<script src="/js/main.js"></script>








	<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
	</script>






  </div>

  <a id="rocket" href="#top" class=""></a>
  <script type="text/javascript" src="/js/totop.js" async=""></script>
</body>
</html>
